/*==============================================================*/
/* DBMS name:      Microsoft SQL Server 2008                    */
/* Created on:     2022-05-27 12:48:06                          */
/*==============================================================*/


if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Comments') and o.name = 'FK_COMMENTS_COMMENTS_MEMBER')
alter table Comments
   drop constraint FK_COMMENTS_COMMENTS_MEMBER
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Comments') and o.name = 'FK_COMMENTS_COMMENTS2_GOODS')
alter table Comments
   drop constraint FK_COMMENTS_COMMENTS2_GOODS
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Goods') and o.name = 'FK_GOODS_RELATIONS_STORE')
alter table Goods
   drop constraint FK_GOODS_RELATIONS_STORE
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('"Order"') and o.name = 'FK_ORDER_ORDER_MEMBER')
alter table "Order"
   drop constraint FK_ORDER_ORDER_MEMBER
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('"Order"') and o.name = 'FK_ORDER_ORDER2_GOODS')
alter table "Order"
   drop constraint FK_ORDER_ORDER2_GOODS
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Recommend') and o.name = 'FK_RECOMMEN_RECOMMEND_MEMBER')
alter table Recommend
   drop constraint FK_RECOMMEN_RECOMMEND_MEMBER
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Recommend') and o.name = 'FK_RECOMMEN_RECOMMEND_RESTAURA')
alter table Recommend
   drop constraint FK_RECOMMEN_RECOMMEND_RESTAURA
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Restaurant') and o.name = 'FK_RESTAURA_MANAGERES_MANAGER')
alter table Restaurant
   drop constraint FK_RESTAURA_MANAGERES_MANAGER
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('ShoppingCart') and o.name = 'FK_SHOPPING_SHOPPINGC_GOODS')
alter table ShoppingCart
   drop constraint FK_SHOPPING_SHOPPINGC_GOODS
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('ShoppingCart') and o.name = 'FK_SHOPPING_SHOPPINGC_MEMBER')
alter table ShoppingCart
   drop constraint FK_SHOPPING_SHOPPINGC_MEMBER
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('Comments')
            and   name  = 'Comments_FK'
            and   indid > 0
            and   indid < 255)
   drop index Comments.Comments_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Comments')
            and   type = 'U')
   drop table Comments
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Goods')
            and   type = 'U')
   drop table Goods
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Manager')
            and   type = 'U')
   drop table Manager
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Member')
            and   type = 'U')
   drop table Member
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('"Order"')
            and   name  = 'Order_FK'
            and   indid > 0
            and   indid < 255)
   drop index "Order".Order_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('"Order"')
            and   type = 'U')
   drop table "Order"
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('Recommend')
            and   name  = 'Recommend_FK'
            and   indid > 0
            and   indid < 255)
   drop index Recommend.Recommend_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Recommend')
            and   type = 'U')
   drop table Recommend
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Restaurant')
            and   type = 'U')
   drop table Restaurant
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('ShoppingCart')
            and   name  = 'ShoppingCart_FK'
            and   indid > 0
            and   indid < 255)
   drop index ShoppingCart.ShoppingCart_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('ShoppingCart')
            and   type = 'U')
   drop table ShoppingCart
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Store')
            and   type = 'U')
   drop table Store
go

if exists(select 1 from systypes where name='Adress')
   drop type Adress
go

if exists(select 1 from systypes where name='Description')
   drop type Description
go

if exists(select 1 from systypes where name='ID')
   drop type ID
go

if exists(select 1 from systypes where name='Location')
   drop type Location
go

if exists(select 1 from systypes where name='Name')
   drop type Name
go

if exists(select 1 from systypes where name='Price')
   drop type Price
go

if exists(select 1 from systypes where name='Quantity')
   drop type Quantity
go

if exists(select 1 from systypes where name='TelephoneNo')
   drop type TelephoneNo
go

if exists(select 1 from systypes where name='Time&Date')
   drop type Time&Date
go

if exists(select 1 from systypes where name='Type')
   drop type Type
go

if exists(select 1 from systypes where name='image')
   drop type image
go

if exists(select 1 from systypes where name='password')
   drop type password
go

/*==============================================================*/
/* Domain: Adress                                               */
/*==============================================================*/
create type Adress
   from varchar(64)
go

/*==============================================================*/
/* Domain: Description                                          */
/*==============================================================*/
create type Description
   from varchar(256)
go

/*==============================================================*/
/* Domain: ID                                                   */
/*==============================================================*/
create type ID
   from char(16)
go

/*==============================================================*/
/* Domain: Location                                             */
/*==============================================================*/
create type Location
   from char(16)
go

/*==============================================================*/
/* Domain: Name                                                 */
/*==============================================================*/
create type Name
   from varchar(32)
go

/*==============================================================*/
/* Domain: Price                                                */
/*==============================================================*/
create type Price
   from money
go

/*==============================================================*/
/* Domain: Quantity                                             */
/*==============================================================*/
create type Quantity
   from int
go

/*==============================================================*/
/* Domain: TelephoneNo                                          */
/*==============================================================*/
create type TelephoneNo
   from varchar(16)
go

/*==============================================================*/
/* Domain: Time&Date                                            */
/*==============================================================*/
create type Time&Date
   from datetime
go

/*==============================================================*/
/* Domain: Type                                                 */
/*==============================================================*/
create type Type
   from varchar(64)
go

/*==============================================================*/
/* Domain: image                                                */
/*==============================================================*/
create type image
   from image
go

/*==============================================================*/
/* Domain: password                                             */
/*==============================================================*/
create type password
   from char(16)
go

/*==============================================================*/
/* Table: Comments                                              */
/*==============================================================*/
create table Comments (
   StoreID              ID                   not null,
   GoodsID              ID                   not null,
   MemberID             ID                   not null,
   CommentsID           ID                   null,
   Comment              varchar(256)         null,
   constraint PK_COMMENTS primary key nonclustered (StoreID, GoodsID, MemberID)
)
go

/*==============================================================*/
/* Index: Comments_FK                                           */
/*==============================================================*/
create index Comments_FK on Comments (
MemberID ASC
)
go

/*==============================================================*/
/* Table: Goods                                                 */
/*==============================================================*/
create table Goods (
   StoreID              ID                   not null,
   GoodsID              ID                   not null,
   GoodsName            Name                 not null,
   GoodsType            Type                 not null,
   GoodsDescription     Description          null,
   GoodsPrice           Price                not null,
   PriceForMember       Price                null,
   GoodsPicture         image                null,
   GoodsClicks          int                  null,
   constraint PK_GOODS primary key nonclustered (StoreID, GoodsID)
)
go

/*==============================================================*/
/* Table: Manager                                               */
/*==============================================================*/
create table Manager (
   MamagerID            ID                   not null,
   LoginName            Name                 not null,
   MamagerPassword      password             not null,
   constraint PK_MANAGER primary key nonclustered (MamagerID)
)
go

/*==============================================================*/
/* Table: Member                                                */
/*==============================================================*/
create table Member (
   MemberID             ID                   not null,
   MemberName           Name                 not null,
   Password             password             not null,
   RealName             Name                 not null,
   TelephoneNo          TelephoneNo          not null,
   Address              Adress               not null,
   MemberIntegral       int                  not null,
   Prepayment           money                null,
   constraint PK_MEMBER primary key nonclustered (MemberID)
)
go

/*==============================================================*/
/* Table: "Order"                                               */
/*==============================================================*/
create table "Order" (
   StoreID              ID                   not null,
   GoodsID              ID                   not null,
   MemberID             ID                   not null,
   OrderID              ID                   null,
   OrderTime            Time&Date            null,
   OrderStatus          int                  null,
   DeliveryType         Type                 null,
   constraint PK_ORDER primary key nonclustered (StoreID, GoodsID, MemberID)
)
go

/*==============================================================*/
/* Index: Order_FK                                              */
/*==============================================================*/
create index Order_FK on "Order" (
MemberID ASC
)
go

/*==============================================================*/
/* Table: Recommend                                             */
/*==============================================================*/
create table Recommend (
   MamagerID            ID                   not null,
   RestaurantID         ID                   not null,
   MemberID             ID                   not null,
   RecommendType        Type                 not null,
   constraint PK_RECOMMEND primary key nonclustered (MamagerID, RestaurantID, MemberID)
)
go

/*==============================================================*/
/* Index: Recommend_FK                                          */
/*==============================================================*/
create index Recommend_FK on Recommend (
MemberID ASC
)
go

/*==============================================================*/
/* Table: Restaurant                                            */
/*==============================================================*/
create table Restaurant (
   MamagerID            ID                   not null,
   RestaurantID         ID                   not null,
   RestaurantName       Name                 not null,
   RestaurantType       Type                 not null,
   OpeningTime          datetime             not null,
   RestaurantDescription Description          not null,
   constraint PK_RESTAURANT primary key nonclustered (MamagerID, RestaurantID)
)
go

/*==============================================================*/
/* Table: ShoppingCart                                          */
/*==============================================================*/
create table ShoppingCart (
   MemberID             ID                   not null,
   StoreID              ID                   not null,
   GoodsID              ID                   not null,
   ShoppingCartID       ID                   null,
   GoodsQuantity        Quantity             null,
   constraint PK_SHOPPINGCART primary key nonclustered (MemberID, StoreID, GoodsID)
)
go

/*==============================================================*/
/* Index: ShoppingCart_FK                                       */
/*==============================================================*/
create index ShoppingCart_FK on ShoppingCart (
StoreID ASC,
GoodsID ASC
)
go

/*==============================================================*/
/* Table: Store                                                 */
/*==============================================================*/
create table Store (
   StoreID              ID                   not null,
   StoreName            Name                 not null,
   Store_X_Y            Location             null,
   StoreType            Type                 null,
   constraint PK_STORE primary key nonclustered (StoreID)
)
go

alter table Comments
   add constraint FK_COMMENTS_COMMENTS_MEMBER foreign key (MemberID)
      references Member (MemberID)
go

alter table Comments
   add constraint FK_COMMENTS_COMMENTS2_GOODS foreign key (StoreID, GoodsID)
      references Goods (StoreID, GoodsID)
go

alter table Goods
   add constraint FK_GOODS_RELATIONS_STORE foreign key (StoreID)
      references Store (StoreID)
go

alter table "Order"
   add constraint FK_ORDER_ORDER_MEMBER foreign key (MemberID)
      references Member (MemberID)
go

alter table "Order"
   add constraint FK_ORDER_ORDER2_GOODS foreign key (StoreID, GoodsID)
      references Goods (StoreID, GoodsID)
go

alter table Recommend
   add constraint FK_RECOMMEN_RECOMMEND_MEMBER foreign key (MemberID)
      references Member (MemberID)
go

alter table Recommend
   add constraint FK_RECOMMEN_RECOMMEND_RESTAURA foreign key (MamagerID, RestaurantID)
      references Restaurant (MamagerID, RestaurantID)
go

alter table Restaurant
   add constraint FK_RESTAURA_MANAGERES_MANAGER foreign key (MamagerID)
      references Manager (MamagerID)
go

alter table ShoppingCart
   add constraint FK_SHOPPING_SHOPPINGC_GOODS foreign key (StoreID, GoodsID)
      references Goods (StoreID, GoodsID)
go

alter table ShoppingCart
   add constraint FK_SHOPPING_SHOPPINGC_MEMBER foreign key (MemberID)
      references Member (MemberID)
go

